﻿using System;
using System.Collections.Generic;
using System.Data;
using BSF.Db;
using HuntingFishGame.ManageDomain.Model.WeChat;

namespace HuntingFishGame.ManageDomain.DAL.WeChat
{
    public class AutoReplyDal
    {
        public static readonly AutoReplyDal Instance = new AutoReplyDal();

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(DbConn pubConn, long id)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("f_id", id);
                string strSql = "select count(1) from tb_AutoReply where f_id=@f_id";
                return LibConvert.ObjToInt(pubConn.ExecuteScalar(strSql.ToString(), ps.ToParameters())) > 0;
            });
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(DbConn pubConn, string keyword, long withoutId = 0)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("f_keyword", keyword);
                ps.Add("withoutId", withoutId);
                string strSql = "select count(1) from tb_AutoReply where f_keyword=@f_keyword and f_id!=@withoutId";
                return LibConvert.ObjToInt(pubConn.ExecuteScalar(strSql.ToString(), ps.ToParameters())) > 0;
            });
        }

        public bool Add(DbConn pubConn, AutoReplyModel item)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql =
                    @"insert into tb_AutoReply(f_keyword,f_content,f_remark,f_sfdj,f_jdrbm,f_jdrmc,f_createtime)" +
                    "values(@f_keyword,@f_content,@f_remark,@f_sfdj,@f_jdrbm,@f_jdrmc,@f_createtime)";
                ps.Add("f_keyword", item.keyword);
                ps.Add("f_content", item.content);
                ps.Add("f_remark", item.remark);
                ps.Add("f_sfdj", item.sfdj);
                ps.Add("f_jdrbm", item.jdrbm);
                ps.Add("f_jdrmc", item.jdrmc);
                ps.Add("f_createtime", item.createtime);
                return LibConvert.ObjToInt(pubConn.ExecuteScalar(strSql.ToString(), ps.ToParameters())) > 0;
            });
            //pubConn.BeginTransaction();
            //try
            //{
            //    List<ProcedureParameter> par = new List<ProcedureParameter>()
            //    {
            //        //new  ProcedureParameter("@f_id",  ProcParType.Int32,4,item.id.ToString()??"0"),
            //        new ProcedureParameter("@f_keyword", ProcParType.VarChar, 50, item.keyword ?? ""),
            //        new ProcedureParameter("@", ProcParType.VarChar, -1, item.content ?? ""),
            //        new ProcedureParameter("@", ProcParType.DateTime, 8,
            //            pubConn.GetServerDate().ToString("yyyy-MM-dd HH:mm:ss"))

            //    };
            //    int rev = pubConn.ExecuteSql("insert into tb_AutoReply(f_keyword,f_content,f_remark,f_sfdj,f_jdrbm,f_jdrmc,f_createtime)" +
            //    "values(@f_keyword,@f_content,@f_remark,@f_sfdj,@f_jdrbm,@f_jdrmc,@f_createtime)", par);
            //    if (rev != 1)
            //    {
            //        pubConn.Rollback();
            //        return false;
            //    }
            //    pubConn.Commit();
            //}
            //catch (Exception ex)
            //{
            //    pubConn.Rollback();
            //    throw ex;
            //}
            return true;

        }

        public bool Update(DbConn pubConn, AutoReplyModel item)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = @"update tb_AutoReply set 
                f_keyword=@f_keyword,  
                f_content=@f_content,  
                f_remark=@f_remark,  
                f_sfdj=@f_sfdj,  
                f_jdrbm=@f_jdrbm,  
                f_jdrmc=@f_jdrmc,  
                f_createtime=@f_createtime 
                " +
                " where f_id=@f_id";
                ps.Add("f_keyword", item.keyword);
                ps.Add("f_content", item.content);
                ps.Add("f_remark", item.remark);
                ps.Add("f_sfdj", item.sfdj);
                ps.Add("f_jdrbm", item.jdrbm);
                ps.Add("f_jdrmc", item.jdrmc);
                ps.Add("f_createtime", item.createtime);
                ps.Add("f_id", item.id);

                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        public bool UpdateState(DbConn pubConn, long id, bool sfdj)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = @"update tb_AutoReply set 
                f_sfdj=@f_sfdj,
                f_createtime=@f_createtime 
                " +
                " where f_id=@f_id";
                ps.Add("f_sfdj", sfdj);
                ps.Add("f_createtime", DateTime.Now);
                ps.Add("f_id", id);

                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="pubConn"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(DbConn pubConn, long id)
        {
            return SqlHelper.Visit(ps =>
            {
                string strSql = "delete from tb_AutoReply where f_id=@f_id";
                ps.Add("@f_id", id);
                return pubConn.ExecuteSql(strSql.ToString(), ps.ToParameters()) > 0;
            });
        }

        public AutoReplyModel GetInfo(DbConn pubConn, long id)
        {
            return SqlHelper.Visit(ps =>
            {
                ps.Add("f_id", id);
                string sql = "select * from tb_AutoReply where f_id=@f_id";
                DataTable table = pubConn.SqlToDataTable(sql, ps.ToParameters());
                if (table != null && table.Rows.Count > 0)
                {
                    return AutoReplyModel.CreateModel(table.Rows[0]);
                }
                else
                {
                    return null;
                }
            });
            //DataSet ds = new DataSet();
            //List<ProcedureParameter> par = new List<ProcedureParameter>() 
            //{
            //    new  ProcedureParameter("@f_id", ProcParType.Int32,20, ParameterDirection.Input, LibConvert.ObjToInt(id)),
            //};
            //pubConn.SqlToDataSet(ds, "select * from tb_AutoReply where f_id=@f_id ", par);

            //if (ds.Tables[0].Rows.Count > 0)
            //{
            //    return AutoReplyModel.CreateModel(ds.Tables[0].Rows[0]);
            //}
            //return null;
        }

        public AutoReplyModel DetailsByField(DbConn pubConn, string field, string fieldval)
        {
            return SqlHelper.Visit(ps =>
            {
                DataSet ds = new DataSet();
                ps.Add("@" + field, fieldval);
                string strWhere = " and f_sfdj=0"; //0-未冻结,1-已冻结
                pubConn.SqlToDataSet(ds,
                    string.Format("select top 1 * from tb_AutoReply where {0}=@{0} {1} ", field, strWhere),
                    ps.ToParameters());

                if (ds.Tables[0].Rows.Count > 0)
                {
                    return AutoReplyModel.CreateModel(ds.Tables[0].Rows[0]);
                }
                else
                {
                    return null;
                }
            });
        }

        #region 分页


        public int TotalItems(DbConn pubConn, string condition, string query)
        {
            try
            {
                return SqlHelper.Visit(ps =>
                {
                    condition = condition ?? "";
                    ps.Add("@condition", condition);

                    #region 查询条件

                    string strWhere = " where 1=1 ";
                    if (!string.IsNullOrEmpty(query))
                        strWhere = string.Concat(strWhere, query);
                    if (!string.IsNullOrEmpty(condition))
                    {
                        strWhere = string.Concat(
                            strWhere,
                            " and ",
                            "(", "f_keyword like '%'+ @condition +'%'", //charindex(@condition,f_keyword)>-1
                            " or ", "f_content like '%'+ @condition +'%'",
                            " or ", "f_remark like '%'+ @condition +'%'",
                            ")"
                            );
                    }

                    #endregion

                    string sql = string.Concat("select count(*) from tb_AutoReply", strWhere);

                    return (int) pubConn.ExecuteScalar(sql, ps.ToParameters());
                });
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public List<AutoReplyModel> GetList(DbConn pubConn, bool? sfdj, DateTime? cstime, DateTime? cetime, string condition, string query, ref int total,
            int pageIndex = 1, int rowSize = 10)
        {
            try
            {
                int _count = 0;
                var models = SqlHelper.Visit(ps =>
                {
                    #region 查询条件

                    string strWhere = " where 1=1 ";
                    if (!string.IsNullOrEmpty(query))
                        strWhere = string.Concat(strWhere, query);
                    condition = condition ?? "";
                    if (!string.IsNullOrEmpty(condition))
                    {
                        strWhere = string.Concat(
                            strWhere,
                            " and ",
                            "(", "f_keyword like '%'+ @condition +'%'",
                            " or ", "f_content like '%'+ @condition +'%'",
                            " or ", "f_remark like '%'+ @condition +'%'",
                            ")"
                            );
                        ps.Add("@condition", condition);
                    }
                    if (sfdj.HasValue)
                    {
                        strWhere = string.Concat(strWhere, " and f_sfdj=@f_sfdj");
                        ps.Add("@f_sfdj", sfdj.Value);
                    }
                    if (cstime.HasValue)
                    {
                        strWhere = string.Concat(strWhere, " and f_createtime>=@cstime");
                        ps.Add("@cstime", cstime.Value);
                    }
                    if (cetime.HasValue)
                    {
                        strWhere = string.Concat(strWhere, " and f_createtime<=@cetime");
                        ps.Add("@cetime", cetime.Value);
                    }
                    #endregion

                    string totalsql = string.Concat("select count(*) from tb_AutoReply", strWhere);

                    _count = (int) pubConn.ExecuteScalar(totalsql, ps.ToParameters());

                    Int32 startIndex = (pageIndex - 1)*rowSize + 1;
                    Int32 endIndex = pageIndex*rowSize;
                    string order = "desc";
                    string sql = String.Concat(
                        " SELECT *  FROM ",
                        " (SELECT row_number() over(order by f_createtime ", order, ") as rownum, ",
                        "* FROM tb_AutoReply",
                        strWhere, ")",
                        " as TEMP ",
                        " WHERE rownum between ", startIndex, " and ", endIndex
                        );
                    DataTable table = pubConn.SqlToDataTable(sql, ps.ToParameters());
                    //pubConn.SqlToDataSet(ds, sql, par);
                    List<AutoReplyModel> modelList = new List<AutoReplyModel>();
                    foreach (DataRow dr in table.Rows)
                    {
                        modelList.Add(AutoReplyModel.CreateModel(dr));
                    }
                    return modelList;
                });
                total = _count;
                return models;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        #endregion
    }
}
